package com.ht.dao;

import com.ht.bean.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

public interface EmpDao {
    //查询员工基本信息
    @Select("SELECT e.*,d.depName,p.postName FROM emp e , dep d , post p  where e.depId = d.depid and e.postid = p.postid")
    List<Map> allEmp();

    @Select({"<script>",
            "SELECT e.*,d.*,p.* FROM emp e , dep d , post p",
            "<where>",
            "e.depId = d.depid and e.postId = p.postId",
            "<if test=\"ep.empId!=null and ep.empId!=''\">",//查ID
            " and empId=#{ep.empId}",
            "</if>",
            "<if test=\"ep.empName!=null and ep.empName!=''\">",//查姓名
            " and empName like concat('%',#{ep.empName},'%')",
            "</if>",
            "<if test=\"ep.depName!=null and ep.depName!=''\">",//查部门
            " and depName like concat('%',#{ep.depName},'%')",
            "</if>",
            "<if test=\"ep.postName!=null and ep.postName!=''\">",//查职务
            " and postName like concat('%',#{ep.postName},'%')",
            "</if>",
            "<if test=\"ep.sex!=null and ep.sex!=''\">",//查性别
            " and sex like concat('%',#{ep.sex},'%')",
            "</if>",
            "<if test=\"ep.phone!=null and ep.phone!=''\">",//查手机号
            " and e.phone  like concat('%',#{ep.phone},'%')",
            "</if>",
            "</where>",
            " limit #{startIndex},#{pageSize}",
            "</script>"})

    List<Map> Emplsit(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize,@Param("ep") Emp emp1);

    //分页查询
    @Select({"<script>",
            "SELECT count(*) FROM emp e , dep d , post p",
            "<where>",
            "e.depId = d.depid and e.postId = p.postId",
            "<if test=\"ep.empId!=null and ep.empId!=''\">",//查ID
            " and empId=#{ep.empId}",
            "</if>",
            "<if test=\"ep.empName!=null and ep.empName!=''\">",//查姓名
            " and empName like concat('%',#{ep.empName},'%')",
            "</if>",
            "<if test=\"ep.depName!=null and ep.depName!=''\">",//查部门
            " and depName like concat('%',#{ep.depName},'%')",
            "</if>",
            "<if test=\"ep.postName!=null and ep.postName!=''\">",//查职务
            " and postName like concat('%',#{ep.postName},'%')",
            "</if>",
            "<if test=\"ep.sex!=null and ep.sex!=''\">",//查性别
            " and sex like concat('%',#{ep.sex},'%')",
            "</if>",
            "<if test=\"ep.phone!=null and ep.phone!=''\">",//查手机号
            " and e.phone  like concat('%',#{ep.phone},'%')",
            "</if>",
            "</where>",
            "</script>"})
    public int countEmp(@Param("ep") Emp emp1);

    //查询员工
    @Select("select * from emp")
    List<Emp> emps();

    //判断电话号码是否存在
//    @Select("select * from emp where phone=#{phone}")
//    boolean phonee(String phone);
    @Select("select count(*) from emp where phone=#{phone}")
    int emp_phone(@Param("phone") String phone);

    //判断身份证是否存在
    @Select("select count(*) from emp where cardNo=#{cardNo}")
    int emp_cardNo(@Param("cardNo") String cardNo);



    //添加员工
    @Insert("insert into emp values (null,#{empName},#{depId},#{sex},#{birthday},#{cardNo},#{nation},#{phone},#{qqCode},#{weixin},#{email},#{married},#{university},#{major},#{education},#{address},#{remark},#{bank},#{accountName},#{bankNumber},#{alipay},#{hireDay},#{fireDay},123456,#{status},#{postId},#{headImg})")
    public boolean addemp(Emp emp1);

    //删除员工
    @Delete("delete from emp where empId=#{empId}")
    boolean delemp(Integer empId);

    //根据id查员工
    @Select("select * from emp where empId=#{empId}")
    Emp allempbyid(Integer empId);

    //查询工作经历---------------------------------------------------------------------------------------------
    @Select("select j.*,e.empName from job j ,emp e where j.empId = e.empId and e.empId=#{empId}")
    List<Map> joblist(Integer empId);

    //添加工作经历
    @Insert("insert into job values (null,#{empId},#{companyName},#{degree},#{startDate},#{endDate},#{reason},#{remark})")
    public boolean addjob(Job job);

    //删除工作经历
    @Delete("delete from job where jobId=#{jobId}")
    boolean deljob(Integer jobId);

    //查询教育背景
    @Select("select d.*,e.empName from education d , emp e where d.empId = e.empId and e.empId=#{empId}")
    List<Map> edulist(Integer empId);

    //添加教育背景
    @Insert("insert into education values (null,#{empId},#{collegeName},#{degree},#{startDate},#{endDate},#{remark})")
    public boolean addedu(Education education);

    //删除教育背景
    @Delete("delete from education where collegeId=#{collegeId}")
    boolean deledu(Integer collegeId);

    //查询家庭联系信息
    @Select("select d.*,e.empName from familyinfo d , emp e where d.empId = e.empId and e.empId=#{empId}")
    List<Map> familylist(Integer empId);

    //添加家庭联系关系
    @Insert("insert into familyinfo values (null,#{empId},#{contactName},#{relationship},#{phone},#{remark})")
    public boolean addfamily(Familyinfo familyinfo);

    //删除家庭联系关系
    @Delete("delete from familyinfo where familyId=#{familyId}")
    boolean delfamily(Integer familyId);

    //查询员工考核
    @Select("select l.*,m.aduitName,e.empName from aduitlog l , aduitmodel m , emp e where l.aduitModelId = m.aduitModelId and l.empId = e.empId and e.empId=#{empId}")
    List<Map> loglist(Integer empId);

    //查询员工考核详情
    @Select("select l.*,m.aduitName,e.empName from aduitlog l , aduitmodel m , emp e where l.aduitModelId = m.aduitModelId and l.empId = e.empId and l.aduitLogId=#{aduitLogId}")
    Map loglistbyid(Integer aduitLogId);

    //查询考核指标
    @Select("select * from aduitmodel")
    List<Map> modellist();

    //添加员工考核
    @Insert("insert into aduitlog values (null,#{aduitModelId},#{empId},#{scores},now(),#{remark},#{auditPerson},#{image})")
    public boolean addlog(Aduitlog aduitlog);

    //删除员工考核
    @Delete("delete from aduitlog where aduitLogId=#{aduitLogId}")
    boolean dellog(Integer aduitLogId);

    //查询员工证件
    @Select("select p.*,e.empName from paper p, emp e where p.empId = e.empId and p.empId=#{empId}")
    List<Map> paperlist(Integer empId);

    //查询员工证件详情
    @Select("select p.*,e.empName from paper p, emp e where p.empId = e.empId and p.imgId=#{imgId}")
    Map paperlistbyid(Integer imgId);

    //添加员工证件
    @Insert("insert into paper values (null,#{empId},#{imgName},#{papers},now(),#{auditPerson},#{remark})")
    public boolean addpaper(Paper paper);

    //删除员工证件
    @Delete("delete from paper where imgId=#{imgId}")
    boolean delpaper(Integer imgId);

    //修改员工信息-------------------------------------------------------------------------------------------------
    //                                                empId, empName, depId, sex, birthday,           cardNo,          nation,        phone,           qqCode,         weixin,        email,          married,            university,             major,           education,         address,            remark,          bank,          accountName,            bankNumber,             alipay,             hireDay,            fireDay,            password,           status,          postId,            headImg
    @Update("update emp set empName=#{empName},depId=#{depId},sex=#{sex},birthday=#{birthday},cardNo=#{cardNo},nation=#{nation},phone=#{phone},qqCode=#{qqCode},weixin=#{weixin},email=#{email},married=#{married},university=#{university},major=#{major},education=#{education},address=#{address},remark=#{remark},bank=#{bank},accountName=#{accountName},bankNumber=#{bankNumber},alipay=#{alipay},hireDay=#{hireDay},fireDay=#{fireDay},password=#{password},status=#{status},postId=#{postId},headImg=#{headImg}  where  empId=#{empId}")
    public boolean updemp(Emp emp1);

    //重置密码
    @Update("update emp set password=123456 where empId=#{empId}")
    boolean czmm(Integer empId);

    //部门-----------------------------------------------------------------------------------------------------------
    //查询部门表
    @Select("select * from dep")
    List<Dep> allDep();

    //查询职务表
    @Select("select * from post")
    List<Post> allpost();

    //查询学生表
    @Select("select * from student")
    List<Map> allstu();


// @Select("select w.*,e.empName,d.depName from weekly w , emp e ,dep d where w.empid = e.empId and e.depId = d.depId")
    //查询周报-----------------------------------------------------------------------------------------------------------------
    @Select({"<script>",
            "select w.*,e.empName,d.depName from weekly w , emp e ,dep d",
            "<where>",
            " w.empId = e.empId and e.depId = d.depid ",
            "<if test=\"we.worklogId!=null and we.worklogId!=''\">",//查ID
            " and worklogId=#{we.worklogId}",
            "</if>",
            "<if test=\"we.empName!=null and we.empName!=''\">",//查员工
            " and empName like concat('%',#{we.empName},'%')",
            "</if>",
            "<if test=\"we.depName!=null and we.depName!=''\">",//查部门
            " and depName like concat('%',#{we.depName},'%')",
            "</if>",
            "</where>",
            " limit #{startIndex},#{pageSize}",
            "</script>"})
//     @Select("select w.*,e.empName,d.depName from weekly w , emp e ,dep d where w.empid = e.empId and e.depId = d.depId limit #{startIndex},#{pageSize}")
    List<Map> allWeekly(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize,@Param("we")Weekly weekly);

    //分页查询
    @Select({"<script>",
            "SELECT count(*) FROM weekly w , emp e ,dep d",
            "<where>",
            " w.empId = e.empId and e.depId = d.depid ",
            "<if test=\"we.worklogId!=null and we.worklogId!=''\">",//查ID
            " and worklogId=#{we.worklogId}",
            "</if>",
            "<if test=\"we.empName!=null and we.empName!=''\">",//查员工
            " and empName like concat('%',#{we.empName},'%')",
            "</if>",
            "<if test=\"we.depName!=null and we.depName!=''\">",//查部门
            " and depName like concat('%',#{we.depName},'%')",
            "</if>",
            "</where>",
            "</script>"})
    public int countWeekly(@Param("we") Weekly weekly);

    //根据id查员工
    @Select("select w.*,e.empName,d.depName from weekly w , emp e ,dep d where w.empid = e.empId and e.depId = d.depId and worklogId=#{worklogId}")
    Map allweeklybyid(Integer worklogId);


//查询谈心记录-----------------------------------------------------------------------
//    @Select("select c.*,s.stuname,e.empName from chatrecord c , student s , emp e  where c.sayface = s.stuId and c.teacher = e.empId limit #{startIndex},#{pageSize}")
    @Select({"<script>",
            "select c.*,s.stuname,e.empName from chatrecord c , student s , emp e  ",
            "<where>",
            " c.sayface = s.stuId and c.teacher = e.empId ",
            "<if test=\"ch.chatId!=null and ch.chatId!=''\">",//查ID
            " and chatId=#{ch.chatId}",
            "</if>",
            "<if test=\"ch.empName!=null and ch.empName!=''\">",//查员工
            " and empName like concat('%',#{ch.empName},'%')",
            "</if>",
            "<if test=\"ch.stuname!=null and ch.stuname!=''\">",//查学生
            " and stuname like concat('%',#{ch.stuname},'%')",
            "</if>",
            "</where>",
            " limit #{startIndex},#{pageSize}",
            "</script>"})
    List<Map> allcord(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize,@Param("ch") Chatrecord chatrecord);

    //谈心分页查询
//    @Select({"<script>",
//            "select count(*) from chatrecord c , student s , emp e  ",
//            " where c.sayface = s.stuId and c.teacher = e.empId ",
//            "</script>"})
    @Select({"<script>",
            "select count(*) from chatrecord c , student s , emp e  ",
            "<where>",
            " c.sayface = s.stuId and c.teacher = e.empId ",
            "<if test=\"ch.chatId!=null and ch.chatId!=''\">",//查ID
            " and chatId=#{ch.chatId}",
            "</if>",
            "<if test=\"ch.empName!=null and ch.empName!=''\">",//查员工
            " and empName like concat('%',#{ch.empName},'%')",
            "</if>",
            "<if test=\"ch.stuname!=null and ch.stuname!=''\">",//查学生
            " and stuname like concat('%',#{ch.stuname},'%')",
            "</if>",
            "</where>",
            "</script>"})
    public int countCord(@Param("ch") Chatrecord chatrecord);

    //添加谈心
    @Insert("insert into chatrecord values (null,#{sayface},#{teacher},#{addr},#{sayscon},now())")
    public boolean addCord(Chatrecord chatrecord);

    //删除谈心记录
    @Delete("delete from chatrecord where chatId=#{chatId}")
    boolean delcord(Integer chatId);

    //历史值班表
    @Select("select w.*,e.empName from weekarrange w , emp e where w.empId = e.empId")
    List<Weekarrange> listange();

    //查看历史值班
    @Select({"<script>",
            "select w.*,e.empName from weekarrange w , emp e  ",
            "<where>",
            "  w.empId = e.empId ",
            "<if test=\"we.weekArrangeId!=null and we.weekArrangeId!=''\">",//查ID
            " and weekArrangeId=#{we.weekArrangeId} ",
            "</if>",
            "<if test=\"we.empName!=null and we.empName!=''\">",//查员工
            " and empName like concat('%',#{we.empName},'%')",
            "</if>",
            "<if test=\"we.weekArrangeName!=null and we.weekArrangeName!=''\">",//查排班名称
            " and weekArrangeName like concat('%',#{we.weekArrangeName},'%')",
            "</if>",
            "<if test=\"we.week!=null and we.week!=''\">",//查星期
            " and week like concat('%',#{we.week},'%')",
            "</if>",
            "<if test=\"we.duty!=null and we.duty!=''\">",//查总值班
            " and duty like concat('%',#{we.duty},'%')",
            "</if>",
            "</where>",
            " limit #{startIndex},#{pageSize} ",
            "</script>"})
    List<Map> allange(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize,@Param("we") Weekarrange weekarrange);

    //分页查看历史
    @Select({"<script>",
            "select count(*) from weekarrange w , emp e  ",
            "<where>",
            "  w.empId = e.empId ",
            "<if test=\"we.weekArrangeId!=null and we.weekArrangeId!=''\">",//查ID
            " and weekArrangeId=#{we.weekArrangeId} ",
            "</if>",
            "<if test=\"we.empName!=null and we.empName!=''\">",//查员工
            " and empName like concat('%',#{we.empName},'%')",
            "</if>",
            "<if test=\"we.weekArrangeName!=null and we.weekArrangeName!=''\">",//查排班名称
            " and weekArrangeName like concat('%',#{we.weekArrangeName},'%')",
            "</if>",
            "<if test=\"we.week!=null and we.week!=''\">",//查星期
            " and week like concat('%',#{we.week},'%')",
            "</if>",
            "<if test=\"we.duty!=null and we.duty!=''\">",//查总值班
            " and duty like concat('%',#{we.duty},'%')",
            "</if>",
            "</where>",
            "</script>"})
    public int countange(@Param("we") Weekarrange weekarrange);

    //历史值班统计图
    @Select("select e.empName as empName,count(w.empId) as empId from weekarrange w , emp e where w.empId=e.empId group by w.empId")
    List<Weekarrange> countMapName();
}
